package com.show.service.sensor;

import com.show.dao.population.PopulationRepository;
import com.show.dto.Histogram;
import com.show.dto.sensor.ShaftCoverSensor;
import com.show.dto.sensor.ShaftCoverSensorError;
import com.show.dto.unit.Unit;
import com.show.dto.unit.UnitType;
import com.show.util.QueryUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

/**
 * 井盖传感器service
 *
 * @author 姜文
 */
@Service
public class ShaftCoverSensorService {

    private QueryUtil queryUtil;

    private PopulationRepository populationRepository;

    @Autowired
    public ShaftCoverSensorService(QueryUtil queryUtil, PopulationRepository populationRepository) {
        this.queryUtil = queryUtil;
        this.populationRepository = populationRepository;
    }


    /**
     * 井盖传感器总数
     *
     * @author 姜文
     */
    public List<ShaftCoverSensor> shaftCoverSensorTotal() {
        String sql = "SELECT * FROM `shaft_cover_sensor`";
        List<ShaftCoverSensor> shaftCoverSensorList = queryUtil.query(sql, ShaftCoverSensor.class);
        return shaftCoverSensorList;
    }

    /**
     * 查询有多少井盖报警
     *
     * @return 数量
     */
    public long shaftErrorCount() {
        String sql = "select * from shaft_cover_sensor_error group by shaft_cover_sensor_id";
        return queryUtil.query(sql, ShaftCoverSensorError.class).size();
    }

    /**
     * 井盖状态监测柱状图
     *
     * @return List
     */
    public List<Histogram> shaftStatusMonitor() {
        List<Histogram> shaftCoverList = new ArrayList<Histogram>();
        List<UnitType> unitTypeList = queryUnitTypeList();
        for (UnitType unitType : unitTypeList) {
            int errorCount = queryErrorCount(unitType.getUnitTypeId());
            int shaftCount = queryShaftCount(unitType.getUnitTypeId());
            int workCount = shaftCount - errorCount;
            Histogram shaftCover = new Histogram(workCount, errorCount, unitType.getUnitTypeName());
            shaftCoverList.add(shaftCover);
        }
        return shaftCoverList;

    }

    /**
     * 查询告警井盖数量
     *
     * @param unitTypeId 井盖类型ID
     * @return 数量
     */
    private int queryErrorCount(String unitTypeId) {
        String sql = "select *\n" +
                "from shaft_cover_sensor_error error\n" +
                "  left join shaft_cover_sensor shaft on error.shaft_cover_sensor_id = shaft.shaft_cover_sensor_id\n" +
                "  left join cg_unit unit on unit.shaft_cover_sensor_id = shaft.shaft_cover_sensor_id\n" +
                "  left join unit_type unitType on unitType.unit_type_id = unit.unit_type_id\n" +
                "where unitType.unit_type_id = '" + unitTypeId + "'\n" +
                "group by error.shaft_cover_sensor_id";
        return queryUtil.query(sql, ShaftCoverSensorError.class).size();
    }

    /**
     * 根据井盖类型查询井盖数量
     *
     * @param unitTypeId 井盖类型ID
     * @return 数量
     */
    private int queryShaftCount(String unitTypeId) {
        String sql = "select * from cg_unit unit where unit.unit_type_id = '" + unitTypeId + "'";
        return queryUtil.query(sql, Unit.class).size();
    }


    /**
     * 获取全部井盖类型
     *
     * @return List
     */
    private List<UnitType> queryUnitTypeList() {
        String sql = "select\n" +
                "  unitType.unit_type_id   unitTypeId,\n" +
                "  unitType.unit_type_name unitTypeName\n" +
                "from unit_type unitType\n" +
                "where sts = '0'";
        return queryUtil.query(sql, UnitType.class);

    }

    /**
     * 查询某天井盖异常次数
     *
     * @param dateString 日期字符串 例如2018-05-13
     * @param status
     * @return count 数量
     */
    public int queryCount(String dateString, String status) {
        String sql = "select * from shaft_cover_sensor_error error where error.status = '" + status + "' and DATE_FORMAT(error.cdate, '%Y-%m-%d') = '" + dateString + "'";
        return queryUtil.query(sql, ShaftCoverSensorError.class).size();
    }


}
